<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"
    %>
<%

//디비 접속 관련 
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@192.168.3.24:1521:xe";
String id = "test";
String pwd = "test";
String driver = "oracle.jdbc.driver.OracleDriver";


//쿼리들
String insertQ = "INSERT INTO DEPTINFO(DEPTNO, DEPTNAME, DEPTSTDATE, DEPTENDATE, DEPTLEVEL, DEPTSTEP, DEPTPRCODE, DEPTCHIEF, ETC) VALUES (?,?,TO_DATE(?,'YYYYMMDD'),TO_DATE(?,'YYYYMMDD'),?,?,?,?,?)";
String updateQ = "UPDATE DEPTINFO SET DEPTNO=?, DEPTNAME=?, DEPTSTDATE=TO_DATE(?,'YYYYMMDD'), DEPTENDATE=TO_DATE(?,'YYYYMMDD'), DEPTLEVEL=?, DEPTSTEP=?, DEPTPRCODE=?, DEPTCHIEF=?, ETC=? WHERE DEPTNO=?";
String deleteQ = "DELETE FROM DEPTINFO WHERE DEPTNO=?";

//시트로부터 넘어온 내용들
String[] sStatus = request.getParameterValues("sStatus");
String[] DEPTNO = request.getParameterValues("DEPTNO");
String[] DEPTNAME = request.getParameterValues("DEPTNAME");
String[] DEPTSTDATE = request.getParameterValues("DEPTSTDATE");
String[] DEPTENDATE = request.getParameterValues("DEPTENDATE");
String[] DEPTLEVEL = request.getParameterValues("DEPTLEVEL");
String[] DEPTSTEP = request.getParameterValues("DEPTSTEP");
String[] DEPTPRCODE = request.getParameterValues("DEPTPRCODE");
String[] DEPTCHIEF = request.getParameterValues("DEPTCHIEF");
String[] ETC = request.getParameterValues("ETC");

try{
	Class.forName(driver);
	conn = DriverManager.getConnection(url,id,pwd);
	conn.setAutoCommit(false);

	for(int row=0;row<sStatus.length;row++){
		System.out.println(sStatus[row]);
		if("I".equals(sStatus[row])){
			pstmt = conn.prepareStatement(insertQ);
			pstmt.setString(1, DEPTNO[row]);
			pstmt.setString(2, DEPTNAME[row]);
			pstmt.setString(3, DEPTSTDATE[row]);
			pstmt.setString(4, DEPTENDATE[row]);
			pstmt.setString(5, DEPTLEVEL[row]);
			pstmt.setString(6, DEPTSTEP[row]);
			pstmt.setString(7, DEPTPRCODE[row]);
			pstmt.setString(8, DEPTCHIEF[row]);
			pstmt.setString(9, ETC[row]);
		}else if("U".equals(sStatus[row])){
			System.out.println(DEPTNO[row]+","+DEPTNAME[row]+","+DEPTSTDATE[row]+","+DEPTENDATE[row]+","+DEPTLEVEL[row]+","+DEPTSTEP[row]+","+DEPTPRCODE[row]+","+DEPTCHIEF[row]+","+ETC[row]+","+DEPTNO[row]);
			pstmt = conn.prepareStatement(updateQ);
			pstmt.setString(1, DEPTNO[row]);
			pstmt.setString(2, DEPTNAME[row]);
			pstmt.setString(3, DEPTSTDATE[row]);
			pstmt.setString(4, DEPTENDATE[row]);
			pstmt.setString(5, DEPTLEVEL[row]);
			pstmt.setString(6, DEPTSTEP[row]);
			pstmt.setString(7, DEPTPRCODE[row]);
			pstmt.setString(8, DEPTCHIEF[row]);
			pstmt.setString(9, ETC[row]);
			pstmt.setString(10, DEPTNO[row]);
		}else{
			pstmt = conn.prepareStatement(deleteQ);
			pstmt.setString(1, DEPTNO[row]);
		}
		pstmt.executeUpdate();
		pstmt.clearParameters();
	}
	conn.commit();
%>
{
  Result : {Code:0, Message:'저장 되었습니다.'}
}
<%
}catch(Exception ex){
	ex.printStackTrace();
	String msg = ex.getMessage();
%>
{
Result : {Code:-1, Message:'저장이 실패하였습니다.\n<%=msg%>'}
}
<%
}finally{
	pstmt.close();
	conn.close();
}
%>

    